/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.MessagingException;
import javax.mail.internet.AddressException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Anna SAELEE
 */
@WebServlet(urlPatterns = {"/ConfirmEmailSending"})
public class Enregistrer extends HttpServlet {

    private String typeMss;
    private String sql;
    private String insrt;
    private String toHome = "<br/> Retourner à la page d'<a href=\"index.jsp\">Acceuille</a>";
    private String noConn = "Ne pas pouvoir connecter au serveur<br/>";
    private String prePage;

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, ClassNotFoundException, SQLException, AddressException, MessagingException {
        response.setContentType("text/html;charset=UTF-8");
        Connection conn;
        Statement stmt;
        ResultSet rs;
        PreparedStatement pst;
        PrintWriter out = response.getWriter();
        try {
            HttpSession session = request.getSession(true);
            String username = (String) session.getAttribute("username");
            session.setAttribute("username", username);

            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dormitorymanagement", "root", "root");
            stmt = conn.createStatement();

            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Répondre du serveur</title>");
            out.println("</head>");
            out.println("<body>");

            typeMss = request.getParameter("typeMss");
            if (typeMss.equals("receiveNews")) {
                String email = request.getParameter("email");
                sql = "SELECT * FROM recieve_news WHERE email='" + email + "'";
                rs = stmt.executeQuery(sql);
                if (!rs.next()) {
                    insrt = "INSERT INTO recieve_news(email) VALUES('" + email + "')";
                    pst = conn.prepareStatement(insrt);
                    int i = pst.executeUpdate();
                    if (i != 0) {
                        ServletContext context = getServletContext();
                        String host = context.getInitParameter("host");
                        String port = context.getInitParameter("port");
                        String user = context.getInitParameter("user");
                        String pass = context.getInitParameter("pass");
                        
                        EmailUtility.sendEmail(host, port, user, pass, email, "Merci de vous inscrire", 
                                "Bonjour Monsieur/Mademoiselle/Madame,"
                                + "\nNous vous remercions de vous inscrire dans notre systeme."
                                + "\nA partir de ce moment la, vous pouvez recevoir des nouvelles de Gestion de la residence d'etudiant."
                                + "\nBien a vous,"
                                + "\nAdministrateur du system Gestion de la residence d'etudiant");
                        out.println("Réussi à s'incrire votre courriel");
                        out.println(toHome);
                    } else {
                        out.println(toHome);
                    }
                } else {
                    out.println("Ce courriel a déjà s'incrit sur ce système");
                    out.println(toHome);
                }
            } else if (typeMss.equals("newAccount")) {
                String usr = request.getParameter("usr");
                String psswd = request.getParameter("psswd");
                String firstname = request.getParameter("firstname");
                String lastname = request.getParameter("lastname");
                String HBD = request.getParameter("date");
                String courriel = request.getParameter("courriel");
                String tel = request.getParameter("tel");
                prePage = "<br/>Retourner à la page d'<a href=\"newStudent.jsp\">Nouveau Compte</a>";

                sql = "SELECT username, firstname, lastname,birthday FROM account WHERE username='" + usr + "' AND firstname='" + firstname + "' AND lastname='" + lastname + "' AND birthday='" + HBD + "'";
                rs = stmt.executeQuery(sql);
                if (!rs.next()) {
                    insrt = "INSERT INTO account(username, password, firstname, lastname, birthday, email, phone_num, status) "
                            + "VALUES('" + usr + "', '" + psswd + "', '" + firstname + "', '" + lastname + "', '" + HBD + "', '" + courriel + "', '" + tel + "', '1')";
                    pst = conn.prepareStatement(insrt);
                    int i = pst.executeUpdate();
                    if (i != 0) {
                        out.println("Réussi à créer nouveau compte d'étudiant.");

                        ServletContext context = getServletContext();
                        String host = context.getInitParameter("host");
                        String port = context.getInitParameter("port");
                        String user = context.getInitParameter("user");
                        String pass = context.getInitParameter("pass");

                        String message = "Bonjour " + lastname + ",<br/>"
                                + "Vous avez recu ce message parce que vous avez donne ce courriel pour creer un compte d'utilisateur dans le system de gestion de la residence d'etudiant.<br/>"
                                + "Vous pouvez cliquer sur "
                                + "<form action=\"http://localhost:8084/DormitoryManagement/Update\" method=\"post\" name=\"confirmMail\">"
                                + "<input type=\"hidden\" name=\"typeMss\" value=\"confirmMail\">"
                                + "<input type=\"hidden\" name=\"username\" value=\"" + usr + "\">"
                                + "<input type=\"submit\" name=\"confirm\" value=\"Confirmation de courriel utilisant dans le system de gestion de la residence d'etudiant.\"/>"
                                + "</form>"
                                + "pour le confirmation.<br/><br/><br/>"
                                + "Mais si vous n'avez pas donner votre compte pour vous inscrire dans notre system,<br/>"
                                + "Vous pouvez cliquer sur "
                                + "<form action=\"http://localhost:8084/DormitoryManagement/Delete\" method=\"post\" name=\"notConfirmMail\">"
                                + "<input type=\"hidden\" name=\"typeMss\" value=\"cancelMail\">"
                                + "<input type=\"hidden\" name=\"username\" value=\"" + usr + "\">"
                                + "<input type=\"submit\" name=\"notConfirm\" value=\"Annuler d'utilisation de courriel dans le system de gestion de la residence d'etudiant.\"/>"
                                + "</form>"
                                + "pour annuler l'utilisation de ce compte.<br/>"
                                + "Merci.";
                        EmailConfirm.sendEmail(host, port, user, pass, courriel, "Confirmation de courriel", message);
                        out.println(toHome);
                        out.println(prePage);
                    } else {
                        out.println(noConn);
                        out.println(toHome);
                    }
                } else {
                    out.println("Vous avez déjà incrit cet étudiant dans le système");
                    out.println(toHome);
                    out.println(prePage);
                }
            } else if (typeMss.equals("newRType")) {
                String typeName = request.getParameter("typeName");
                String t_typePlace = request.getParameter("typePlace");
                int typePlace = Integer.parseInt(t_typePlace);
                String typeInfo = request.getParameter("typeInfo");
                prePage = "<br/>Retourner à la page de <a href=\"room.jsp\">Type de chambre</a>";

                sql = "SELECT tname FROM type WHERE tname='" + typeName + "'";
                rs = stmt.executeQuery(sql);
                if (!rs.next()) {
                    insrt = "INSERT INTO type(tname, place, detail) VALUES('" + typeName + "', " + typePlace + ", '" + typeInfo + "')";
                    pst = conn.prepareStatement(insrt);
                    int i = pst.executeUpdate();
                    if (i != 0) {
                        out.println("Réussi à créer nouveau type de chambre");
                        out.println(toHome);
                        out.println(prePage);
                    } else {
                        out.println(noConn);
                        out.println(toHome);
                    }
                } else {
                    out.println("Vous avez déjà incrit ce type de chambre dans le système");
                    out.println(toHome);
                    out.println(prePage);
                }
            } else if (typeMss.equals("newRoom")) {
                String numberRoom = request.getParameter("numberRoom");
                String typeRoom = request.getParameter("typeRoom");
                String floorRoom = request.getParameter("floorRoom");
                prePage = "<br/>Retourner à la page de <a href=\"room.jsp\">Type de chambre</a>";

                sql = "SELECT place FROM type WHERE tname='" + typeRoom + "'";
                rs = stmt.executeQuery(sql);
                if (rs.next()) {
                    String t_place = rs.getString("place");
                    int place = Integer.parseInt(t_place);
                    sql = "SELECT r_number FROM room WHERE r_number='" + numberRoom + "'";
                    rs = stmt.executeQuery(sql);
                    if (!rs.next()) {
                        insrt = "INSERT INTO room(r_number, tname, floor, n_place, available) "
                                + "VALUES('" + numberRoom + "', '" + typeRoom + "', '" + floorRoom + "', 0, " + place + ")";
                        pst = conn.prepareStatement(insrt);
                        int i = pst.executeUpdate();
                        if (i != 0) {
                            out.println("Réussi à créer nouvelle chambre");
                            out.println(toHome);
                            out.println(prePage);
                        } else {
                            out.println(noConn);
                            out.println(toHome);
                        }
                    } else {
                        out.println("Il y a déjà cette chambre: " + numberRoom + " dans le système");
                        out.println(toHome);
                        out.println(prePage);
                    }
                } else {
                    out.println(noConn);
                    out.println(toHome);
                    out.println(prePage);
                }
            } else if (typeMss.equals("newAnnounce")) {
                prePage = "<br/>Retourner à la page <a href=\"announce.jsp\">Créer nouvelle annonce</a>";

                String category = request.getParameter("category");
                if (category.equals("newCategory")) {
                    category = request.getParameter("otherCategory");
                    sql = "SELECT cname FROM category WHERE cname='" + category + "'";
                    rs = stmt.executeQuery(sql);
                    if (!rs.next()) {
                        insrt = "INSERT INTO category(cname, type) VALUES('" + category + "', 'news')";
                        pst = conn.prepareStatement(insrt);
                        int i = pst.executeUpdate();
                        if (i == 1) {
                            out.println("Réussi à créer nouveau type de catégorie<br/>");
                            sql = "SELECT cid FROM category WHERE cname='" + category + "'";
                            rs = stmt.executeQuery(sql);
                            if (rs.next()) {
                                category = rs.getString("cid");
                            }
                        } else {
                            out.println(noConn);
                            out.println(toHome);
                            out.println(prePage);
                        }
                    } else {
                        out.println("Catégorie '" + category + "' est déjà existé dans le système");
                        out.println(prePage);
                    }
                }

                String date = request.getParameter("date");
                String subject = request.getParameter("subject");
                String detail = request.getParameter("detail");
                insrt = "INSERT INTO news(cid, date, username, subject, detail) "
                        + "VALUES('" + category + "', '" + date + "', '" + username + "', '" + subject + "', '" + detail + "')";
                pst = conn.prepareStatement(insrt);
                int j = pst.executeUpdate();
                if (j == 1) {
                    ServletContext context = getServletContext();
                    String host = context.getInitParameter("host");
                    String port = context.getInitParameter("port");
                    String user = context.getInitParameter("user");
                    String pass = context.getInitParameter("pass");

                    sql = "SELECT email FROM account";
                    rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        EmailUtility.sendEmail(host, port, user, pass, rs.getString("email"), subject, detail);
                    }
                    
                    sql = "SELECT email FROM recieve_news";
                    rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        EmailUtility.sendEmail(host, port, user, pass, rs.getString("email"), subject, detail);
                    }
                    out.println("Réussi à créer nouveau announce<br/>");
                    out.println(toHome);
                    out.println(prePage);
                } else {
                    out.println(noConn);
                    out.println(toHome);
                    out.println(prePage);
                }
            } else if (typeMss.equals("newService")) {
                prePage = "<br/>Retourner à la page <a href=\"listService.jsp\">List des services</a>";

                String serviceName = request.getParameter("serviceName");
                if (!serviceName.equals("")) {
                    sql = "SELECT cid FROM category WHERE cname='" + serviceName + "'";
                    rs = stmt.executeQuery(sql);
                    if (!rs.next()) {
                        insrt = "INSERT INTO category(cname, type) VALUES('" + serviceName + "', 'service')";
                        pst = conn.prepareStatement(insrt);
                        int i = pst.executeUpdate();
                        if (i == 1) {
                            out.println("Réussi à créer nouveau service dans le système<br/>");
                            out.println(toHome);
                            out.println(prePage);
                        } else {
                            out.println(noConn);
                            out.println(toHome);
                        }
                    } else {
                        out.println("Il y a déjà ce service dans le système");
                        out.println(toHome);
                        out.println(prePage);
                    }
                } else {
                    out.println("Il faut que vous ajoutez le nom pour le service.");
                    out.println(prePage);
                }
            } else if (typeMss.equals("orderService")) {
                String message = request.getParameter("message");
                session.setAttribute("message", message);
                prePage = "<br/>Retourner à la page <a href=\"service.jsp\">Demander des services</a>";

                String t_category = request.getParameter("category");
                int category = Integer.parseInt(t_category);
                if (message.equals("")) {
                    out.println("Il faut ajouter quelque message, s'il vous plaît!");
                    out.println(toHome);
                    out.println(prePage);
                } else if (category == 0) {
                    out.println("Choissisez-vous un catégorie, s'il vous plaît!");
                    out.println(toHome);
                    out.println(prePage);
                } else {
                    String date = request.getParameter("date");
                    String room = request.getParameter("room");
                    session.setAttribute("message", message);
                    insrt = "INSERT INTO service(date, s_username, r_username, r_number, cid, message, status) "
                            + "VALUES('" + date + "', '" + username + "', 'admin', '" + room + "', " + category + ", \"" + message + "\", 'n')";
                    pst = conn.prepareStatement(insrt);
                    int i = pst.executeUpdate();
                    if (i != 0) {
                        out.println("Réussi à envoyer votre demande vers l'administrateur");
                        out.println(toHome);
                        out.println(prePage);
                        session.removeAttribute("message");
                    } else {
                        out.println(noConn);
                        out.println(toHome);
                        session.removeAttribute("message");
                    }
                }
            } else if (typeMss.equals("reponseService")) {
                prePage = "<br/>Retourner à la page <a href=\"listOrder.jsp\">Liste des demandes de services</a>";

                String date = request.getParameter("date");
                String reciever = request.getParameter("reciever");
                String room = request.getParameter("room");
                String category = request.getParameter("category");
                String message = request.getParameter("message");
                insrt = "INSERT INTO service(date, s_username, r_username, r_number, cid, message, status) "
                        + "VALUES('" + date + "', '" + username + "', '" + reciever + "', '" + room + "', " + category + ", \"" + message + "\", 'n')";
                pst = conn.prepareStatement(insrt);
                int i = pst.executeUpdate();
                if (i != 0) {
                    out.println("Réussi à envoyer la réponse vers utilisateur " + reciever + ".");
                    out.println(toHome);
                    out.println(prePage);
                } else {
                    out.println(noConn);
                    out.println(toHome);
                }
            }

            out.println("</body>");
            out.println("</html>");
        } finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (AddressException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (MessagingException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (AddressException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (MessagingException ex) {
            Logger.getLogger(Enregistrer.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
